/*
 * 创建日期 2004-12-14
 *
 * 更改所生成文件模板为
 * 窗口 > 首选项 > Java > 代码生成 > 代码和注释
 *************************/
package net.comm.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import net.comm.util.DbcpPool;

import org.apache.log4j.Logger;



/**
 * @author QLZhao
 *
 * 更改所生成类型注释的模板为
 * 窗口 > 首选项 > Java > 代码生成 > 代码和注释
 */
public class DBFunction 
{
	static Logger logger = Logger.getLogger(DBFunction.class.getName());
	
	/**
	 * 执行传入的update语句，返回更新的记录数
	 * @param strSql
	 * @param CConn
	 * @return 更新记录数
	 * @throws Exception
	 */
	public static int updateDBCount(String strSql, Connection CConn) throws Exception
	{
		int iCount = 0;
		Statement stmt = null;
		
		if ( null == strSql || null == CConn )
		{

			logger.warn("DB"+"传入参数不正确");
			iCount = -1;
		}
		else
		{
//			Logger.debug("updateDBCount","strSql=["+strSql+"]");
			
			try
			{
				stmt = CConn.createStatement();
				iCount = stmt.executeUpdate(strSql);
			}
			catch ( SQLException sqle )
			{
				logger.error("DB"+"SQLException, SQL:["+strSql+"]");
				logger.error("DB"+"SQLException, errcode:["+sqle.getErrorCode()+"]");
				throw sqle;
			}
			finally
			{
				if ( null != stmt )
				{
					stmt.close();
				}
			}
		}
		
		return iCount;
	}
	
	/**
	 * 传入SQL语句、字段名、字段类型、数据库连接，传出单个对应类型的Object
	 * @param strSql
	 * @param strFieldID
	 * @param strType
	 * @param CConn
	 * @return Object(int-->Integer；double-->Double；String-->String)
	 * @throws Exception
	 * 该方法可以查询单笔记录的一个字段，也可以实现返回一个统计值的统计功能
	 * 
	 */
	public static Object getDBObject(String strSql, String strFieldID, String strType, Connection CConn) throws Exception
	{
		Object CObj = null;
		Integer CInt = null;
		String Cstr = null;
		Double CDbl = null;
		ResultSet rs = null;
		Statement stmt = null;
		
		if ( null == strSql || null == strFieldID || null == strType || null == CConn )
		{
			logger.warn("DB"+"传入参数不正确");
			CObj = null;
		}
		else
		{
			logger.debug("getDBObject"+"strSql=["+strSql+"]");
			logger.debug("getDBObject"+"strFieldID=["+strFieldID+"]");
			logger.debug("getDBObject"+"strType=["+strType+"]");
			
			try
			{
				stmt = CConn.createStatement();
				rs = stmt.executeQuery(strSql);
				
				if (rs.next())
				{
					if ( strType.trim().equals("String") )
					{
						Cstr = rs.getString(strFieldID);
						CObj = Cstr;
					}
					else
					if ( strType.trim().equals("int") )
					{
						CInt = new Integer( rs.getInt(strFieldID) );
						CObj = CInt;
					}
					else
					if ( strType.trim().equals("double") )
					{
						CDbl = new Double( rs.getDouble(strFieldID) );
						CObj = CDbl;
					}
					else
					{
						logger.warn("DB"+"传入类型不支持");
						CObj = null;
					}
				}
				else
				{
					logger.warn("DB"+"SQL语句未查得记录");
					CObj = null;
				}
			}
			catch ( SQLException sqle )
			{
				logger.error("DB"+"SQLException, SQL:["+strSql+"]");
				logger.error("DB"+"SQLException, errcode:["+sqle.getErrorCode()+"]");
				throw sqle;
			}
			finally
			{
				if ( null != stmt )
				{
					stmt.close();
				}
			
				if ( null != rs )
				{
					rs.close();
				}
			}
		}
		
		return CObj;
	}
	
	/**
	 * 传入SQL语句、字段名、字段类型、数据库连接，传出单个对应类型的Object
	 * @param strSql
	 * @param strFieldID
	 * @param strType
	 * @param CConn
	 * @return
	 * @throws Exception
	 */
	public static Object getSUMObject(String strSql, String strFieldID, String strType, Connection CConn) throws Exception
	{
		
		return DBFunction.getDBObject(strSql, strFieldID, strType, CConn );
		
		/*
		Object CObj = null;
		Integer CInt = null;
		String Cstr = null;
		Double CDbl = null;
		ResultSet rs = null;
		Statement stmt = null;
		
		if ( null == strSql || null == strFieldID || null == strType || null == CConn )
		{
			Logger.warn("DB","传入参数不正确");
			CObj = null;
		}
		else
		{
			try
			{
				stmt = CConn.createStatement();
				rs = stmt.executeQuery(strSql);
				
				if (rs.next())
				{
					if ( strType.trim().equals("String") )
					{
						Cstr = rs.getString(strFieldID);
						CObj = Cstr;
					}
					else
					if ( strType.trim().equals("int") )
					{
						CInt = new Integer( rs.getInt(strFieldID) );
						CObj = CInt;
					}
					else
					if ( strType.trim().equals("double"))
					{
						CDbl = new Double( rs.getDouble(strFieldID) );
						CObj = CDbl;
					}
					else
					{
						Logger.warn("DB","传入类型不支持");
						CObj = null;
					}
				}
				else
				{
					Logger.warn("DB","SQL语句未查得记录");
					CObj = null;
				}
			}
			catch ( Exception e )
			{
				throw e;
			}
			finally
			{
				if ( null != stmt )
				{
					stmt.close();
				}
			
				if ( null != rs )
				{
					rs.close();
				}
			}
		}
		
		return CObj;
		*/
	}
	
	/**
	 * 执行传入的查询语句，返回结果集
	 * @param strSql:完整的sql语句
	 * @param CConn:外部传入连接
	 * @return ResultSet:查询结果集
	 * @throws Exception
	 * 该方法供后台服务使用
	 */
	public static ResultSet getResultSet(String strSql, Connection CConn) throws Exception
	{
		Statement stmt = null;
		ResultSet rs = null;
		
		if (null == strSql||null == CConn)
		{
			rs = null;
		}
		else
		{
			try
			{
				stmt = CConn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
				rs=stmt.executeQuery(strSql);
			}
			catch ( SQLException sqle )
			{
				sqle.printStackTrace();
				logger.error("DB"+"SQLException, SQL:["+strSql+"]");
				logger.error("DB"+"SQLException, errcode:["+sqle.getErrorCode()+"]");
				throw sqle;
			}
		}
		return rs;
	}
	
	/**
	 * 执行传入的查询语句，返回结果集
	 * @param strSql:完整的sql语句
	 * @param CConn:外部传入连接
	 * @return ResultSet:查询结果集
	 * @throws Exception
	 * 该方法供后台服务使用
	 */
	public static ResultSet getFirstResultSet(String strSql, Connection CConn) throws Exception
	{
		Statement stmt = null;
		ResultSet rs = null;
		
		if (null == strSql||null == CConn)
		{
			rs = null;
		}
		else
		{			
			try
			{
				stmt = CConn.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
				rs=stmt.executeQuery(strSql);
			}
			catch ( SQLException sqle )
			{
				sqle.printStackTrace();
				logger.error("DB"+"SQLException, SQL:["+strSql+"]");
				logger.error("DB"+"SQLException, errcode:["+sqle.getErrorCode()+"]");
				throw sqle;
			}
		}
		return rs;
	}
	
	/**
	 * 执行传入的查询语句，返回结果集
	 * @param strSql:完整的sql语句
	 * @param CConn:外部传入连接
	 * @return ResultSet:查询结果集
	 * @throws Exception
	 * 该方法供后台服务使用
	 */
	public static ResultSet getPreResultSet(String strSql,Connection CConn) throws Exception
	{
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		if (null == strSql||null == CConn)
		{
			rs = null;
		}
		else
		{			
			try
			{
				stmt = CConn.prepareStatement(strSql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
				rs=stmt.executeQuery();
			}
			catch ( SQLException sqle )
			{
				sqle.printStackTrace();
				logger.error("DB"+"SQLException, SQL:["+strSql+"]");
				logger.error("DB"+"SQLException, errcode:["+sqle.getErrorCode()+"]");
				throw sqle;
			}
		}
		return rs;
	}
	
	/**
	 * 执行传入的查询语句，返回结果集，游标可来回游动
	 * @param strSql:完整的sql语句
	 * @param CConn:外部传入连接
	 * @return ResultSet:查询结果集
	 * @throws Exception
	 * add by yczheng 20060913
	 */
	public static ResultSet getResultSetScroll(String strSql,Connection CConn) throws Exception
	{
		Statement stmt = null;
		ResultSet rs = null;
		
		if (null == strSql||null == CConn)
		{
			rs = null;
		}
		else
		{			
			try
			{
				stmt = CConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
				rs=stmt.executeQuery(strSql);
			}
			catch ( SQLException sqle )
			{
				sqle.printStackTrace();
				logger.error("DB"+"SQLException, SQL:["+strSql+"]");
				logger.error("DB"+"SQLException, errcode:["+sqle.getErrorCode()+"]");
				throw sqle;
			}
		}
		return rs;
	}
	
	/**
	 * 执行传入的更新语句
	 * @param strSql:完整的sql语句
	 * @param CConn:外部传入连接
	 * @return ResultSet:查询结果集
	 * @throws Exception
	 * 该方法供后台服务使用
	 */
	public static void updateDbRecord(String strSql, Connection CConn) throws Exception
	{
		Statement stmt = null;
		
		if (null == strSql||null == CConn)
		{
		}
		else
		{			
			try
			{
				stmt = CConn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
				stmt.executeUpdate(strSql);
			}
			catch ( SQLException sqle )
			{
				sqle.printStackTrace();
				logger.error("DB"+"SQLException, SQL:["+strSql+"]");
				logger.error("DB"+"SQLException, errcode:["+sqle.getErrorCode()+"]");
				throw sqle;
			}
			finally{
				try{
					if (stmt!=null) stmt.close();
				}
				catch(Exception e)
				{
					e.printStackTrace();	
				}
			}
		}
	}
	
	/**
	 * 执行传入的更新语句
	 * @param strSql:完整的sql语句
	 * @param CConn:外部传入连接
	 * @return ResultSet:查询结果集
	 * @throws Exception
	 * 该方法供后台服务使用
	 */
	public static void updatePreDbRecord(String strSql, Connection CConn) throws Exception
	{
		PreparedStatement stmt = null;
		
		if (null == strSql||null == CConn)
		{
		}
		else
		{			
			try
			{
				stmt = CConn.prepareStatement(strSql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
				stmt.executeUpdate();
			}
			catch ( SQLException sqle )
			{
				sqle.printStackTrace();
				logger.error("DB"+"SQLException, SQL:["+strSql+"]");
				logger.error("DB"+"SQLException, errcode:["+sqle.getErrorCode()+"]");
				throw sqle;
			}
			finally{
				try{
					if (stmt!=null) stmt.close();
				}
				catch(Exception e)
				{
					e.printStackTrace();	
				}
			}
		}
	}
	
	//释放连接；
  	public static void returnRS(ResultSet rs)
  	{
    	try {
      		if (rs != null && rs.getStatement()!=null ) rs.getStatement().close();
      		if (rs != null) rs.close();
    	}
    	catch (SQLException e) {
      		e.printStackTrace() ;
    	}
    	finally{
    		rs=null;
    	}
  	}
  	
  	/**
  	 * 从连接获取Statement
  	 * @param cn	数据库连接标识符
  	 * @return Statement
  	 * @throws DBProcException
  	 */
  	public static Statement createStatement(Connection cn) throws DBProcException
  	{
  		Statement stmt = null;
  		
  		try
  		{
  			if(cn == null) {
  				throw new DBProcException(-1 , "传入的数据库连接为空");
  			}
  			
  			stmt = cn.createStatement();
  			return stmt;
  			
  		} catch(SQLException ex) 
  		{
  			ex.printStackTrace();
  			throw new DBProcException(ex.getErrorCode() , ex.getMessage());
  		}
  	}
  	
  	/**
  	 * 关闭statement
  	 * @param stmt	Statement句柄
  	 * @return boolean
  	 * @throws SQLException
  	 */
  	public static boolean closeStatement(Statement stmt) throws SQLException
  	{
  		if(stmt != null) 
  		{
  			stmt.close();
  			return true;
  		}
  		
  		return false;
  	}
  	
  	/**
  	 * 关闭ResultSet
  	 * @param stmt	Statement句柄
  	 * @return boolean
  	 * @throws SQLException
  	 */
  	public static boolean closeRS(ResultSet rs) throws SQLException
  	{
  		if(rs != null) 
  		{
  			rs.close();
  			return true;
  		}
  		
  		return false;
  	}
  	
  	public static void main(String[] args) 
  	{
  		Connection cn  = null;
  		Statement stmt = null;
  		ResultSet rs = null;
  		
  		long begin = System.currentTimeMillis();
  		try
  		{
  			cn = DbcpPool.getConnection();
  			System.out.println("获取连接时间:" + String.valueOf(System.currentTimeMillis() - begin));
  			begin = System.currentTimeMillis();
  			DbcpPool.beginTran(cn);
  			System.out.println("开始事务时间:" + String.valueOf(System.currentTimeMillis() - begin));
  			begin = System.currentTimeMillis();
  			stmt = createStatement(cn);
  			System.out.println("创建statement时间:" + String.valueOf(System.currentTimeMillis() - begin));
  			begin = System.currentTimeMillis();
  			rs = stmt.executeQuery("select * from paramnostro");
  			System.out.println("查询时间:" + String.valueOf(System.currentTimeMillis() - begin));
  			begin = System.currentTimeMillis();
  			rs.next();
  			rs.getString("nostro");
  			System.out.println("获取数据时间:" + String.valueOf(System.currentTimeMillis() - begin));
  			begin = System.currentTimeMillis();
  			closeRS(rs);
  			System.out.println("关闭rs时间:" + String.valueOf(System.currentTimeMillis() - begin));
  			begin = System.currentTimeMillis();
  			closeStatement(stmt);
  			System.out.println("关闭stmt时间:" + String.valueOf(System.currentTimeMillis() - begin));
  			begin = System.currentTimeMillis();
  			DbcpPool.commitTran(cn);
  			System.out.println("提交事务时间:" + String.valueOf(System.currentTimeMillis() - begin));
  			begin = System.currentTimeMillis();
  			DbcpPool.closeTran(cn);
  			System.out.println("关闭事务时间:" + String.valueOf(System.currentTimeMillis() - begin));
  			begin = System.currentTimeMillis();
  			DbcpPool.returnConnection(cn);
  			System.out.println("关闭连接:" + String.valueOf(System.currentTimeMillis() - begin));
  			begin = System.currentTimeMillis();
  			
  		} catch(SQLException ex) {
  			ex.printStackTrace();
  			try {
  				DbcpPool.rollBackTran(cn);
  			} catch(Exception ex1) {ex1.printStackTrace();}
  		} catch(DBProcException ex) {
  			ex.printStackTrace();
  			try {
  				DbcpPool.rollBackTran(cn);
  			} catch(Exception ex1) {ex1.printStackTrace();}
  		} catch(Exception ex) {
  			ex.printStackTrace();
  			try {
  				DbcpPool.rollBackTran(cn);
  			} catch(Exception ex1) {ex1.printStackTrace();}
  		} finally {
  			try {
  				begin = System.currentTimeMillis();
  				DbcpPool.rollBackTran(cn);
  				System.out.println("回滚事务:" + String.valueOf(System.currentTimeMillis() - begin));
  	  			begin = System.currentTimeMillis();
  	  			DbcpPool.close(cn);
  				System.out.println("关闭连接:" + String.valueOf(System.currentTimeMillis() - begin));
  	  			begin = System.currentTimeMillis();
  			} catch(Exception ex1) {ex1.printStackTrace();}
  			
  		}
  		
  		System.out.println(System.currentTimeMillis() - begin);
	}

}
